﻿using Microsoft.SqlServer.Server;
using QingZhouLinYe_Web.Models;
using SqlSugar;
using System;
using System.Collections.Generic;
//using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Web;

namespace QingZhouLinYe_Web.DbContext
{
    public class DbHelper
    {
        private static SqlSugarClient Db;
        public static SqlSugarClient GetDb()
        {
            Db = new SqlSugarClient(new ConnectionConfig
            {
                ConnectionString = BaseClass.DbCoon,
                DbType = DbType.SqlServer,
                InitKeyType = InitKeyType.Attribute,
                //IsAutoCloseConnection = true},
                IsAutoCloseConnection = true
            },
            Db =>
            {
                //配置使用这个类型
                //NpgsqlConnection.GlobalTypeMapper.UseNetTopologySuite();


            });
            Db.Aop.OnLogExecuting = (sql, pars) =>
            {
                //Debug.WriteLine(sql + "\r\n" + Db.Utilities.SerializeObject(pars.ToDictionary(it => it.ParameterName, it => it.Value)));
                //Debug.WriteLine("sql语句:\r\n"+sql + "\r\n");
            };
            return Db;
        }



        public static void CreateSysDataBase()
        {

            using (SqlSugarClient db = GetDb())
            {
                db.DbMaintenance.CreateDatabase();   //创建数据库
                List<String> dbs = db.DbMaintenance.GetDataBaseList();  //获取所有数据库名称
                if (dbs.Exists(s => s != "QingZhouLinYe_test"))   //判断数据库是否存在
                {
                   
                }
                //创建表
                db.CodeFirst.InitTables(
                    typeof(QuHua),
                    typeof(FangHuoDanWei),
                    typeof(SheBei),
                    typeof(SheBeiLeiXing),
                    typeof(HuoQing),
                    typeof(HuoQingLeiBie),
                    typeof(HuoQingZhiBei),
                    typeof(HuoQingZhuangTai),
                    typeof(UserInfo)
                 );

                //修改SheBei表中Shape字段为geometry类型
                //string alterShebeiShapeSql = @"ALTER TABLE SheBei ALTER COLUMN Shape geometry";
                //db.Ado.ExecuteCommand(alterShebeiShapeSql);
                //修改QuHua表中CenterShape、BorderShape字段为geometry类型
                //string alterQuHuaShapeSql = @"ALTER TABLE QuHua ALTER COLUMN CenterShape geometry;ALTER TABLE QuHua ALTER COLUMN BorderShape geometry";
                //db.Ado.ExecuteCommand(alterQuHuaShapeSql);
                //修改FangHuoDanWei表中CenterShape、BorderShape字段为geometry类型
                //string alterFangHuoDanWeiShapeSql = @"ALTER TABLE FangHuoDanWei ALTER COLUMN CenterShape geometry;ALTER TABLE FangHuoDanWei ALTER COLUMN BorderShape geometry";
                //db.Ado.ExecuteCommand(alterFangHuoDanWeiShapeSql);



                //初始化基础数据
                {
                    //设备类型
                    List<SheBeiLeiXing> sblxs = new List<SheBeiLeiXing>();
                    sblxs.Add(new SheBeiLeiXing { LeiXing = "手持终端" });
                    sblxs.Add(new SheBeiLeiXing { LeiXing = "车载终端" });
                    db.Fastest<SheBeiLeiXing>().BulkCopyAsync(sblxs);


                    //区划和防火单位初始化
                    List<QuHua> qhs = new List<QuHua>();
                    QuHua qh1 = new QuHua();
                    qh1.UpId = 0;
                    qh1.MingCheng = "潍坊市";
                    db.Insertable(qh1).ExecuteCommand();
                    qhs.Add(qh1);
                    QuHua qh2 = new QuHua();
                    qh2.UpId = 1;
                    qh2.MingCheng = "青州市";
                    db.Insertable(qh2).ExecuteCommand();
                    qhs.Add(qh2);
                    db.Fastest<QuHua>().BulkCopyAsync(qhs);

                    List<FangHuoDanWei> fhdws = new List<FangHuoDanWei>();
                    FangHuoDanWei fhdw1 = new FangHuoDanWei();
                    fhdw1.QuHuaId = 2;
                    fhdw1.MingCheng = "防灭火巡查专业队";
                    //fhdw1.CenterShape = Geometry.Point(118.334,36.594,4490);
                    fhdw1.MapLevel = 12;
                    db.Insertable(fhdw1).ExecuteCommand();
                    fhdws.Add(fhdw1);
                    FangHuoDanWei fhdw2 = new FangHuoDanWei();
                    fhdw2.QuHuaId = 2;
                    fhdw2.MingCheng = "王坟镇";
                    //fhdw2.CenterShape = Geometry.Point(118.434, 36.494, 4490);
                    fhdw2.MapLevel = 12;
                    db.Insertable(fhdw2).ExecuteCommand();
                    fhdws.Add(fhdw2); 
                    FangHuoDanWei fhdw3 = new FangHuoDanWei();
                    fhdw3.QuHuaId = 2;
                    fhdw3.MingCheng = "庙子镇";
                    //fhdw3.CenterShape = Geometry.Point(118.534, 36.394, 4490);
                    fhdw3.MapLevel = 12;
                    db.Insertable(fhdw3).ExecuteCommand();
                    fhdws.Add(fhdw3);
                    db.Fastest<FangHuoDanWei>().BulkCopyAsync(fhdws);

                    //网页登录用户信息
                    UserInfo userInfo = new UserInfo();
                    userInfo.FangHuoDanWeiId = 1;
                    userInfo.Name = "admin";
                    userInfo.PassWord = "admin1";
                    userInfo.Authority = 0;
                    db.Insertable(userInfo).ExecuteCommandAsync();

                    string[] leibeis = {"地表火", "树冠火" ,"地下火","草塘火"};
                    List<HuoQingLeiBie> lbs = new List<HuoQingLeiBie>();
                    foreach (string str1 in leibeis)
                    {
                        HuoQingLeiBie huoQingLeiBie = new HuoQingLeiBie();
                        huoQingLeiBie.LeiBie = str1;
                        Debug.WriteLine("huoqing leibie is :" + huoQingLeiBie.LeiBie);
                        lbs.Add(huoQingLeiBie);
                    }
                    db.Fastest<HuoQingLeiBie>().BulkCopyAsync(lbs);

                    string[] zhibeis = {"灌木丛","草丛", "幼龄阔叶林", "幼龄针叶林", "阔叶林","针叶林" };
                    List<HuoQingZhiBei> zbs = new List<HuoQingZhiBei>();
                    foreach (string str in zhibeis)
                    {
                        HuoQingZhiBei huoQingZhiBei = new HuoQingZhiBei();
                        huoQingZhiBei.ZhiBei = str;
                        Debug.WriteLine("huoqing zhibei is :" + huoQingZhiBei.ZhiBei);
                        zbs.Add(huoQingZhiBei);
                    }
                    db.Fastest<HuoQingZhiBei>().BulkCopyAsync(zbs);

                    string[] huoqingzhuangtais = {"未处置", "处置中", "已处置"};
                    List<HuoQingZhuangTai> zts = new List<HuoQingZhuangTai>();
                    foreach (string str2 in huoqingzhuangtais)
                    {
                        HuoQingZhuangTai huoQingZhuangTai = new HuoQingZhuangTai();
                        huoQingZhuangTai.ZhuangTai = str2;
                        Debug.WriteLine("huoqing zhuangtai is :" + huoQingZhuangTai.ZhuangTai);
                        zts.Add(huoQingZhuangTai);
                    }
                    db.Fastest<HuoQingZhuangTai>().BulkCopyAsync(zts);
                }
                Debug.WriteLine("QingZhouLinYe_test数据库创建成功！");
            }
        }

    }
}